/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.campaign.dao;

import com.aptech.campaign.entities.Campaign;
import com.aptech.campaign.entities.Report;
import com.aptech.campaign.util.ConfigureDB;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;

/**
 *
 * @author Tai Nguyen
 */
public class ReportDAO {

    private String lasterror;
    String sql = "";
    Connection con = null;
    ConfigureDB db = null;
    CachedRowSet crs = null;

    public ReportDAO() {
        db = new ConfigureDB();
    }

    /**
     * get Arraylist Campaign have finished
     * @param
     * @return ArrayList<Campaign> arr
     */
    public ArrayList<Campaign> readByAllcampaign() {
        ArrayList<Campaign> arr = new ArrayList<Campaign>();
        con = db.getConnection();
        sql = "select a.CampaignID, b.CampaignName from ActualBudget a JOIN Campaign b on a.CampaignID = b.CampaignID where b.Status = ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setBoolean(1, true);
            crs.execute(con);
            while (crs.next()) {
                Campaign cam = new Campaign();
                cam.setCampaignID(crs.getInt(1));
                cam.setCampaignName(crs.getString(2));
                arr.add(cam);
            }
            return arr;
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return null;
    }

    /**
     * get SaleTarget of a campaign
     * @param int campaignID
     * @return the float value
     */
    public float readBySaleTarget(int campaignID) {
        con = db.getConnection();
        sql = "select SaleTarget from [EstimatedBudget] where CampaignID = ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setInt(1, campaignID);
            crs.execute(con);
            while (crs.next()) {
                return crs.getFloat(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return 0;
    }

    /**
     * get ActualSale of a campaign
     * @param int campaignID
     * @return the float value
     */
    public float readByActualSale(int campaignID) {
        con = db.getConnection();
        sql = "select ActualSale from [ActualBudget] where CampaignID = ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setInt(1, campaignID);
            crs.execute(con);
            while (crs.next()) {
                return crs.getFloat(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return 0;
    }

    /**
     * get BudgetAllocation of a campaign
     * @param int campaignID
     * @return the float value
     */
    public float readByBudgetAllocation(int campaignID) {
        con = db.getConnection();
        sql = "select BudgetAllocation from [EstimatedBudget] where CampaignID = ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setInt(1, campaignID);
            crs.execute(con);
            while (crs.next()) {
                return crs.getFloat(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return 0;
    }

    /**
     * get ActualBudget of a campaign
     * @param int campaignID
     * @return the float value
     */
    public float readByActualBudget(int campaignID) {
        con = db.getConnection();
        sql = "select ActualBudget from [ActualBudget] where CampaignID = ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setInt(1, campaignID);
            crs.execute(con);
            while (crs.next()) {
                return crs.getFloat(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return 0;
    }

    /**
     * get all data of campaigns in a give time frame
     * @param Date firstDate, Date secondDate
     * @reutrn ArrayList<Report> arr
     */
    public ArrayList<Report> readByDate(Date firstDate, Date secondDate) {
        ArrayList<Report> arr = new ArrayList<Report>();
        con = db.getConnection();
        sql = "select a.CampaignID, b.CampaignName, d.ProductName, c.SaleTarget, a.ActualSale, c.SaleVariance as EstimateVariance, a.SaleVariance as ActualVariance "
                + "from ActualBudget a JOIN Campaign b on a.CampaignID = b.CampaignID  JOIN EstimatedBudget c on a.CampaignID  = c.CampaignID join Product d on b.ProductID = d.ProductID "
                + "where b.StartDate >= ? and b.EndDate <= ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setDate(1, firstDate);
            crs.setDate(2, secondDate);
            crs.execute(con);
            while (crs.next()) {
                Report re = new Report();
                re.setCampaign(crs.getString(2));
                re.setProduct(crs.getString(3));
                re.setSaleTarget(crs.getFloat(4));
                re.setActualSale(crs.getFloat(5));
                re.setEstimateVariance(crs.getFloat(6));
                re.setActualVariance(crs.getFloat(7));
                arr.add(re);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     * @return the lasterror
     */
    public String getLasterror() {
        return lasterror;
    }

    /**
     * @param lasterror the lasterror to set
     */
    public void setLasterror(String lasterror) {
        this.lasterror = lasterror;
    }
}
